{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 36,
   "id": "2370ecf5",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "请输入选手信息表名称选手信息\n",
      "工作簿已读取\n",
      "请输入新工作簿名称：123\n",
      "信息录入完成\n"
     ]
    }
   ],
   "source": [
    "from openpyxl import Workbook\n",
    "import random\n",
    "old_name = input(\"请输入选手信息表名称\")\n",
    "wb = load_workbook('./' + old_name + '.xlsx')\n",
    "sheet_name = wb.sheetnames[0]\n",
    "ws = wb[sheet_name]\n",
    "xs_dic = {}\n",
    "for row in range(1,ws.max_row+1):\n",
    "    xs_list = []\n",
    "    key=ws['A' + str(row)].value\n",
    "    w1=ws['B' + str(row)].value\n",
    "    w2=ws['C' + str(row)].value\n",
    "    w3=ws['D' + str(row)].value\n",
    "    xs_list=[w1,w2,w3]\n",
    "    xs_dic[key] = xs_list\n",
    "print('工作簿已读取')\n",
    "nums_list=[]\n",
    "result_list=[]\n",
    "coaches=8\n",
    "columns=10\n",
    "for i in range(coaches):\n",
    "    scor=0\n",
    "    aver=0\n",
    "    nums_list=[]\n",
    "    result_list.append([])\n",
    "    for j in range(columns):\n",
    "        num=random.randint(0,10)\n",
    "        nums_list.append(num)\n",
    "    nums_list.sort()\n",
    "    del nums_list[0]\n",
    "    del nums_list[-1]\n",
    "    aver=sum(nums_list)/len(nums_list)\n",
    "    scor=round(aver * xs_dic['s0' + str(i+1)][2],3)\n",
    "    result_list[i].append('s0' + str(i+1))\n",
    "    for n in range(3):\n",
    "        result_list[i].append(dic['s0' + str(i+1)][n])\n",
    "    result_list[i].append(aver)\n",
    "    result_list[i].append(scor)\n",
    "last_result=sorted(result_list,key=lambda e:e[5],reverse=True)\n",
    "for k in range(coaches):\n",
    "    last_result[k].append(k+1)\n",
    "wb=Workbook()\n",
    "sheet=wb.active\n",
    "sheet.title='成绩表'\n",
    "sheet_field=['选手ID','姓名','国家','难度系数','平均分','最后得分','名次']\n",
    "sheet.append(sheet_field)\n",
    "for l in last_result:\n",
    "    sheet.append(l)\n",
    "new_name = input(\"请输入新工作簿名称：\")\n",
    "wb.save(new_name+'.xlsx')\n",
    "print('信息录入完成')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "400019e6",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
